package io.renren.dao;

import io.renren.utils.DBHelper;
import org.apache.commons.lang.StringUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @className: MapperWithJDBCDao
 * @program: renren-generator
 * @description: jdbc操作数据
 * @author: wzp12
 * @create: 2019-06-04 08:14
 **/
public class MapperWithJDBCDao implements SysGeneratorDao {

    private DBHelper dbHelper = new DBHelper();

    @Override
    public List<Map<String, Object>> queryList(Map<String, Object> map) {
        List<Map<String, Object>> list = new ArrayList<>();

        try {

            // 创建sql
            StringBuilder sb = new StringBuilder("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables where table_schema = (select database()) ");

            if (map.get("tableName") != null && StringUtils.isNotBlank(map.get("tableName").toString())) {
                sb.append(MessageFormat.format(" and table_name like ''%{0}%'' ", map.get("tableName")));
            }
            sb.append("order by create_time desc ");
            if (map.get("offset") != null && map.get("limit") != null && StringUtils.isNotBlank(map.get("offset").toString()) && StringUtils.isNotBlank(map.get("limit").toString())) {
                sb.append(MessageFormat.format(" limit {0}, {1} ", map.get("offset"), map.get("limit")));
            }

            ResultSet resultSet = dbHelper.query(sb.toString());

            List<Map<String, Object>> mapList = new ArrayList<>();
            while (resultSet.next()) {
                mapList.add(new HashMap<String, Object>() {{
                    put("tableName", resultSet.getString("tableName"));
                    put("engine", resultSet.getString("engine"));
                    put("tableComment", resultSet.getString("tableComment"));
                    put("createTime", resultSet.getString("createTime"));
                    put("tableName", resultSet.getString("tableName"));
                }});
            }
            dbHelper.connectionClose();

            return mapList;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public int queryTotal(Map<String, Object> map) {
        int count = 0;

        try {
            // 创建sql
            StringBuilder sb = new StringBuilder("select count(*) 'count' from information_schema.tables where table_schema = (select database()) ");
            if (map.get("tableName") != null && StringUtils.isNotBlank(map.get("tableName").toString())) {
                sb.append(MessageFormat.format(" and table_name like ''%{0}%'' ", map.get("tableName")));
            }

            ResultSet resultSet = dbHelper.query(sb.toString());

            if (resultSet.next()) {
                count = resultSet.getInt("count");
            }
            dbHelper.connectionClose();
            return count;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public Map<String, String> queryTable(String tableName) {

        HashMap<String, String> table = new HashMap<>();

        if (StringUtils.isBlank(tableName)) {
            return null;
        }

        try {

            // 创建sql
            ResultSet resultSet = dbHelper.query(
                    MessageFormat.format("select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables where table_schema = (select database()) and table_name = ''{0}'' ",
                    tableName));

            while (resultSet.next()) {
                table = new HashMap<String, String>() {{
                    put("tableName", resultSet.getString("tableName"));
                    put("engine", resultSet.getString("engine"));
                    put("tableComment", resultSet.getString("tableComment"));
                    put("createTime", resultSet.getString("createTime"));
                    put("tableName", resultSet.getString("tableName"));
                }};
            }
            dbHelper.connectionClose();
            return table;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Map<String, String>> queryColumns(String tableName) {

        List<Map<String, String>> list = new ArrayList<>();

        try {

            // 创建sql
            ResultSet resultSet = dbHelper.query(
                    MessageFormat.format("select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.columns where table_name = ''{0}'' and table_schema = (select database()) order by ordinal_position",
                    tableName));

            List<Map<String, String>> mapList = new ArrayList<>();
            while (resultSet.next()) {
                mapList.add(new HashMap<String, String>() {{
                    put("columnName", resultSet.getString("columnName"));
                    put("dataType", resultSet.getString("dataType"));
                    put("columnComment", resultSet.getString("columnComment"));
                    put("columnKey", resultSet.getString("columnKey"));
                    put("extra", resultSet.getString("extra"));
                }});
            }
            dbHelper.connectionClose();

            return mapList;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return list;
    }

}
